{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 基于日期时间的分组聚合\n",
    "\n",
    "本教程介绍如何基于日期时间进行分组聚合操作，包括时间窗口聚合、滚动窗口操作和时间相关的透视表。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "from datetime import datetime, timedelta"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1. 基于时间周期的分组"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 创建示例数据\n",
    "np.random.seed(42)\n",
    "dates = pd.date_range('2023-01-01', '2023-12-31', freq='D')\n",
    "n_records = len(dates) * 3  # 每天3条记录\n",
    "\n",
    "# 生成交易数据\n",
    "data = {\n",
    "    'timestamp': np.random.choice(dates, n_records),\n",
    "    'product': np.random.choice(['A', 'B', 'C', 'D'], n_records),\n",
    "    'region': np.random.choice(['北京', '上海', '广州', '深圳'], n_records),\n",
    "    'sales': np.random.normal(1000, 200, n_records),\n",
    "    'quantity': np.random.randint(1, 20, n_records)\n",
    "}\n",
    "\n",
    "df = pd.DataFrame(data)\n",
    "df['timestamp'] = pd.to_datetime(df['timestamp'])\n",
    "df = df.sort_values('timestamp').reset_index(drop=True)\n",
    "\n",
    "print(\"示例交易数据:\")\n",
    "print(df.head(10))\n",
    "print(f\"\\n数据形状: {df.shape}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 按不同时间周期分组聚合\n",
    "df.set_index('timestamp', inplace=True)\n",
    "\n",
    "# 按月份分组\n",
    "monthly_agg = df.groupby(df.index.month).agg({\n",
    "    'sales': ['sum', 'mean', 'count'],\n",
    "    'quantity': ['sum', 'mean']\n",
    "})\n",
    "\n",
    "print(\"按月份聚合:\")\n",
    "print(monthly_agg.head())\n",
    "\n",
    "# 按季度分组\n",
    "quarterly_agg = df.groupby(df.index.quarter).agg({\n",
    "    'sales': ['sum', 'mean', 'std'],\n",
    "    'quantity': 'sum'\n",
    "})\n",
    "\n",
    "print(\"\\n按季度聚合:\")\n",
    "print(quarterly_agg)\n",
    "\n",
    "# 按星期几分组\n",
    "weekday_agg = df.groupby(df.index.dayofweek).agg({\n",
    "    'sales': ['mean', 'count'],\n",
    "    'quantity': 'mean'\n",
    "})\n",
    "weekday_agg.index = ['周一', '周二', '周三', '周四', '周五', '周六', '周日']\n",
    "\n",
    "print(\"\\n按星期几聚合:\")\n",
    "print(weekday_agg)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2. Grouper对象的使用"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 使用Grouper进行时间分组\n",
    "# 重置索引以使用Grouper\n",
    "df_reset = df.reset_index()\n",
    "\n",
    "# 按月分组\n",
    "monthly_grouper = df_reset.groupby(pd.Grouper(key='timestamp', freq='M')).agg({\n",
    "    'sales': ['sum', 'mean', 'count'],\n",
    "    'quantity': 'sum',\n",
    "    'product': lambda x: x.nunique()  # 产品种类数\n",
    "})\n",
    "\n",
    "print(\"使用Grouper按月分组:\")\n",
    "print(monthly_grouper.head())\n",
    "\n",
    "# 按周分组\n",
    "weekly_grouper = df_reset.groupby(pd.Grouper(key='timestamp', freq='W')).agg({\n",
    "    'sales': 'sum',\n",
    "    'quantity': 'sum'\n",
    "})\n",
    "\n",
    "print(f\"\\n按周分组结果数量: {len(weekly_grouper)}\")\n",
    "print(\"前5周数据:\")\n",
    "print(weekly_grouper.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 多维度分组（时间 + 类别）\n",
    "# 按月份和产品分组\n",
    "monthly_product = df_reset.groupby([\n",
    "    pd.Grouper(key='timestamp', freq='M'),\n",
    "    'product'\n",
    "]).agg({\n",
    "    'sales': 'sum',\n",
    "    'quantity': 'sum'\n",
    "})\n",
    "\n",
    "print(\"按月份和产品分组:\")\n",
    "print(monthly_product.head(20))\n",
    "\n",
    "# 按季度和地区分组\n",
    "quarterly_region = df_reset.groupby([\n",
    "    pd.Grouper(key='timestamp', freq='Q'),\n",
    "    'region'\n",
    "]).agg({\n",
    "    'sales': ['sum', 'mean'],\n",
    "    'quantity': 'sum'\n",
    "})\n",
    "\n",
    "print(\"\\n按季度和地区分组:\")\n",
    "print(quarterly_region)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3. 滚动窗口聚合"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 创建日度汇总数据用于滚动窗口分析\n",
    "daily_summary = df.groupby(df.index.date).agg({\n",
    "    'sales': 'sum',\n",
    "    'quantity': 'sum'\n",
    "})\n",
    "daily_summary.index = pd.to_datetime(daily_summary.index)\n",
    "\n",
    "print(\"日度汇总数据:\")\n",
    "print(daily_summary.head(10))\n",
    "\n",
    "# 滚动窗口统计\n",
    "rolling_stats = pd.DataFrame({\n",
    "    'sales': daily_summary['sales'],\n",
    "    'sales_ma7': daily_summary['sales'].rolling(7).mean(),\n",
    "    'sales_ma30': daily_summary['sales'].rolling(30).mean(),\n",
    "    'sales_std7': daily_summary['sales'].rolling(7).std(),\n",
    "    'sales_min7': daily_summary['sales'].rolling(7).min(),\n",
    "    'sales_max7': daily_summary['sales'].rolling(7).max()\n",
    "})\n",
    "\n",
    "print(\"\\n滚动窗口统计（前15天）:\")\n",
    "print(rolling_stats.head(15))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 自定义滚动函数\n",
    "def rolling_volatility(x):\n",
    "    \"\"\"计算滚动波动率\"\"\"\n",
    "    return x.std() / x.mean() if x.mean() != 0 else 0\n",
    "\n",
    "def rolling_growth_rate(x):\n",
    "    \"\"\"计算滚动增长率\"\"\"\n",
    "    return (x.iloc[-1] - x.iloc[0]) / x.iloc[0] * 100 if len(x) > 1 and x.iloc[0] != 0 else 0\n",
    "\n",
    "# 应用自定义滚动函数\n",
    "custom_rolling = pd.DataFrame({\n",
    "    'sales': daily_summary['sales'],\n",
    "    'volatility_7d': daily_summary['sales'].rolling(7).apply(rolling_volatility),\n",
    "    'growth_rate_7d': daily_summary['sales'].rolling(7).apply(rolling_growth_rate),\n",
    "    'range_7d': daily_summary['sales'].rolling(7).apply(lambda x: x.max() - x.min()),\n",
    "    'median_7d': daily_summary['sales'].rolling(7).median()\n",
    "})\n",
    "\n",
    "print(\"自定义滚动统计（前20天）:\")\n",
    "print(custom_rolling.head(20))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4. 扩展窗口聚合"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 扩展窗口统计（累积统计）\n",
    "expanding_stats = pd.DataFrame({\n",
    "    'sales': daily_summary['sales'],\n",
    "    'cumulative_mean': daily_summary['sales'].expanding().mean(),\n",
    "    'cumulative_sum': daily_summary['sales'].expanding().sum(),\n",
    "    'cumulative_std': daily_summary['sales'].expanding().std(),\n",
    "    'cumulative_max': daily_summary['sales'].expanding().max(),\n",
    "    'cumulative_min': daily_summary['sales'].expanding().min()\n",
    "})\n",
    "\n",
    "print(\"扩展窗口统计（每10天显示一次）:\")\n",
    "print(expanding_stats.iloc[::10].head(20))\n",
    "\n",
    "# 计算累积增长率\n",
    "expanding_stats['cumulative_growth'] = (\n",
    "    (expanding_stats['sales'] - expanding_stats['sales'].iloc[0]) / \n",
    "    expanding_stats['sales'].iloc[0] * 100\n",
    ")\n",
    "\n",
    "print(\"\\n累积增长率（年末数据）:\")\n",
    "print(expanding_stats[['sales', 'cumulative_growth']].tail(10))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5. 时间相关的透视表"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 创建时间透视表\n",
    "# 添加时间相关的列\n",
    "df_pivot = df.reset_index()\n",
    "df_pivot['year'] = df_pivot['timestamp'].dt.year\n",
    "df_pivot['month'] = df_pivot['timestamp'].dt.month\n",
    "df_pivot['quarter'] = df_pivot['timestamp'].dt.quarter\n",
    "df_pivot['weekday'] = df_pivot['timestamp'].dt.dayofweek\n",
    "\n",
    "# 月份 vs 产品的销售透视表\n",
    "monthly_product_pivot = df_pivot.pivot_table(\n",
    "    values='sales',\n",
    "    index='month',\n",
    "    columns='product',\n",
    "    aggfunc='sum',\n",
    "    fill_value=0\n",
    ")\n",
    "\n",
    "print(\"月份 vs 产品销售透视表:\")\n",
    "print(monthly_product_pivot)\n",
    "\n",
    "# 季度 vs 地区的销售透视表\n",
    "quarterly_region_pivot = df_pivot.pivot_table(\n",
    "    values='sales',\n",
    "    index='quarter', \n",
    "    columns='region',\n",
    "    aggfunc=['sum', 'mean'],\n",
    "    fill_value=0\n",
    ")\n",
    "\n",
    "print(\"\\n季度 vs 地区销售透视表:\")\n",
    "print(quarterly_region_pivot)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 星期几 vs 产品的透视表\n",
    "weekday_product_pivot = df_pivot.pivot_table(\n",
    "    values=['sales', 'quantity'],\n",
    "    index='weekday',\n",
    "    columns='product',\n",
    "    aggfunc='mean'\n",
    ")\n",
    "\n",
    "# 重命名星期几\n",
    "weekday_names = ['周一', '周二', '周三', '周四', '周五', '周六', '周日']\n",
    "weekday_product_pivot.index = [weekday_names[i] for i in weekday_product_pivot.index]\n",
    "\n",
    "print(\"星期几 vs 产品透视表（平均销售额）:\")\n",
    "print(weekday_product_pivot['sales'])\n",
    "\n",
    "print(\"\\n星期几 vs 产品透视表（平均数量）:\")\n",
    "print(weekday_product_pivot['quantity'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 6. 时间序列分解和趋势分析"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 时间序列趋势分析\n",
    "# 按月聚合数据\n",
    "monthly_trend = df_reset.groupby(pd.Grouper(key='timestamp', freq='M')).agg({\n",
    "    'sales': 'sum',\n",
    "    'quantity': 'sum'\n",
    "})\n",
    "\n",
    "# 计算月度增长率\n",
    "monthly_trend['sales_growth'] = monthly_trend['sales'].pct_change() * 100\n",
    "monthly_trend['quantity_growth'] = monthly_trend['quantity'].pct_change() * 100\n",
    "\n",
    "# 计算移动平均趋势\n",
    "monthly_trend['sales_trend'] = monthly_trend['sales'].rolling(3).mean()\n",
    "monthly_trend['sales_seasonal'] = monthly_trend['sales'] - monthly_trend['sales_trend']\n",
    "\n",
    "print(\"月度趋势分析:\")\n",
    "print(monthly_trend.round(2))\n",
    "\n",
    "# 年度对比分析\n",
    "if len(monthly_trend) >= 12:\n",
    "    # 计算同比增长（如果有足够数据）\n",
    "    monthly_trend['yoy_growth'] = monthly_trend['sales'].pct_change(12) * 100\n",
    "    \n",
    "    print(\"\\n包含同比增长的月度分析:\")\n",
    "    print(monthly_trend[['sales', 'sales_growth', 'yoy_growth']].round(2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 7. 实际应用示例"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 电商平台销售分析\n",
    "# 创建更复杂的电商数据\n",
    "np.random.seed(42)\n",
    "dates = pd.date_range('2023-01-01', '2023-12-31', freq='H')  # 小时级数据\n",
    "n_orders = len(dates) // 2  # 每2小时平均1个订单\n",
    "\n",
    "ecommerce_data = {\n",
    "    'order_time': np.random.choice(dates, n_orders),\n",
    "    'customer_id': np.random.randint(1000, 9999, n_orders),\n",
    "    'product_category': np.random.choice(['电子产品', '服装', '家居', '图书', '食品'], n_orders),\n",
    "    'order_value': np.random.lognormal(6, 1, n_orders),  # 对数正态分布的订单金额\n",
    "    'payment_method': np.random.choice(['支付宝', '微信', '信用卡', '银行卡'], n_orders),\n",
    "    'city': np.random.choice(['北京', '上海', '广州', '深圳', '杭州', '成都'], n_orders)\n",
    "}\n",
    "\n",
    "ecommerce_df = pd.DataFrame(ecommerce_data)\n",
    "ecommerce_df['order_time'] = pd.to_datetime(ecommerce_df['order_time'])\n",
    "ecommerce_df = ecommerce_df.sort_values('order_time').reset_index(drop=True)\n",
    "\n",
    "print(\"电商订单数据:\")\n",
    "print(ecommerce_df.head())\n",
    "print(f\"\\n总订单数: {len(ecommerce_df)}\")\n",
    "print(f\"订单金额统计: {ecommerce_df['order_value'].describe()}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 多维度时间分析\n",
    "# 1. 小时级别的订单模式\n",
    "hourly_pattern = ecommerce_df.groupby(ecommerce_df['order_time'].dt.hour).agg({\n",
    "    'order_value': ['count', 'sum', 'mean'],\n",
    "    'customer_id': 'nunique'\n",
    "})\n",
    "hourly_pattern.columns = ['订单数', '总金额', '平均金额', '客户数']\n",
    "\n",
    "print(\"24小时订单模式:\")\n",
    "print(hourly_pattern.round(2))\n",
    "\n",
    "# 找出订单高峰时段\n",
    "peak_hours = hourly_pattern['订单数'].nlargest(3)\n",
    "print(f\"\\n订单高峰时段: {peak_hours.index.tolist()}时\")\n",
    "\n",
    "# 2. 周度分析\n",
    "weekly_analysis = ecommerce_df.groupby([\n",
    "    pd.Grouper(key='order_time', freq='W'),\n",
    "    'product_category'\n",
    "]).agg({\n",
    "    'order_value': ['sum', 'count'],\n",
    "    'customer_id': 'nunique'\n",
    "})\n",
    "\n",
    "print(\"\\n周度产品类别分析（前20行）:\")\n",
    "print(weekly_analysis.head(20))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 客户行为分析\n",
    "# 客户购买频次分析\n",
    "customer_behavior = ecommerce_df.groupby('customer_id').agg({\n",
    "    'order_time': ['count', 'min', 'max'],\n",
    "    'order_value': ['sum', 'mean']\n",
    "})\n",
    "\n",
    "customer_behavior.columns = ['订单次数', '首次购买', '最后购买', '总消费', '平均订单']\n",
    "customer_behavior['购买天数'] = (customer_behavior['最后购买'] - customer_behavior['首次购买']).dt.days + 1\n",
    "customer_behavior['购买频率'] = customer_behavior['订单次数'] / customer_behavior['购买天数']\n",
    "\n",
    "print(\"客户行为分析（前10名客户）:\")\n",
    "print(customer_behavior.head(10))\n",
    "\n",
    "# 客户价值分层\n",
    "customer_segments = pd.cut(\n",
    "    customer_behavior['总消费'], \n",
    "    bins=[0, 1000, 5000, 10000, float('inf')],\n",
    "    labels=['低价值', '中价值', '高价值', '超高价值']\n",
    ")\n",
    "\n",
    "segment_analysis = customer_behavior.groupby(customer_segments).agg({\n",
    "    '订单次数': 'mean',\n",
    "    '总消费': 'mean',\n",
    "    '平均订单': 'mean',\n",
    "    '购买频率': 'mean'\n",
    "})\n",
    "\n",
    "print(\"\\n客户价值分层分析:\")\n",
    "print(segment_analysis.round(2))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 可视化分析结果\n",
    "fig, axes = plt.subplots(2, 2, figsize=(15, 10))\n",
    "\n",
    "# 1. 24小时订单模式\n",
    "hourly_pattern['订单数'].plot(kind='bar', ax=axes[0,0], title='24小时订单分布')\n",
    "axes[0,0].set_xlabel('小时')\n",
    "axes[0,0].set_ylabel('订单数')\n",
    "axes[0,0].tick_params(axis='x', rotation=0)\n",
    "\n",
    "# 2. 月度销售趋势\n",
    "monthly_sales = ecommerce_df.groupby(pd.Grouper(key='order_time', freq='M'))['order_value'].sum()\n",
    "monthly_sales.plot(ax=axes[0,1], title='月度销售趋势', marker='o')\n",
    "axes[0,1].set_ylabel('销售额')\n",
    "\n",
    "# 3. 产品类别销售占比\n",
    "category_sales = ecommerce_df.groupby('product_category')['order_value'].sum()\n",
    "category_sales.plot(kind='pie', ax=axes[1,0], title='产品类别销售占比', autopct='%1.1f%%')\n",
    "\n",
    "# 4. 城市销售对比\n",
    "city_sales = ecommerce_df.groupby('city')['order_value'].sum().sort_values(ascending=True)\n",
    "city_sales.plot(kind='barh', ax=axes[1,1], title='城市销售对比')\n",
    "axes[1,1].set_xlabel('销售额')\n",
    "\n",
    "plt.tight_layout()\n",
    "plt.show()\n",
    "\n",
    "# 输出关键指标\n",
    "print(\"\\n关键业务指标:\")\n",
    "print(f\"总订单数: {len(ecommerce_df):,}\")\n",
    "print(f\"总销售额: ¥{ecommerce_df['order_value'].sum():,.2f}\")\n",
    "print(f\"平均订单金额: ¥{ecommerce_df['order_value'].mean():.2f}\")\n",
    "print(f\"独立客户数: {ecommerce_df['customer_id'].nunique():,}\")\n",
    "print(f\"客户平均订单数: {len(ecommerce_df) / ecommerce_df['customer_id'].nunique():.1f}\")\n",
    "print(f\"最受欢迎的产品类别: {category_sales.idxmax()}\")\n",
    "print(f\"销售额最高的城市: {city_sales.idxmax()}\")"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "name": "python",
   "version": "3.8.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
